﻿
CREATE PROCEDURE [dbo].[fares_FiltreazaListaStocuri] 
	@filtruArt varchar(8000),
	@filtruCat varchar(8000),
	@laData datetime,
	@StocuriZero bit,
	@Comparatie smallint,
	@ValoareStoc decimal(25,5),
	@CodUnitate smallint
AS
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
    -- Insert statements for procedure here
    SELECT *,v.Stoc*v.ValoareUnitara ValoareTotala FROM
    (SELECT art.IDArticol,st.Pret ValoareUnitara,
    art.Denumire DenumireArticol,tca.Denumire DenumireCategorie, um.Denumire DenumireUM,
    st.StocInitial StocInitial,
    st.StocPrecedent StocPrecedent,
    dbo.GetStocIntrariData(st.IDArticol,st.Pret,@laData) Intrari ,
    dbo.GetStocIesiriData(st.IDArticol,st.Pret,@laData,fs.GestiuneCantitativa) Iesiri,
    st.StocInitial + st.StocPrecedent+dbo.GetStocIntrariData(st.IDArticol,st.Pret,@laData)- dbo.GetStocIesiriData(st.IDArticol,st.Pret,@laData,fs.GestiuneCantitativa) Stoc
    FROM [Articole] art
    JOIN [TipCategoriiArticole] tca ON art.IDCategorie = tca.IDCategorie
    LEFT OUTER JOIN [Stocuri] st ON st.IDArticol = art.IDArticol 
    JOIN UnitatiMasura um ON art.IDUnitateMasura = um.IDUnitateMasura
    JOIN FaresSetup fs ON fs.CodUnitate = art.CodUnitate
    WHERE 
    art.CodUnitate = @CodUnitate AND
    (LEN(LTRIM(RTRIM(@filtruArt)))=0 OR (art.IDArticol IN (SELECT fart.value FROM dbo.SplitIntParameters(@filtruArt,',') fart))) AND
    (LEN(LTRIM(RTRIM(@filtruCat)))=0 OR (tca.IDCategorie IN (SELECT CAST(fart.value as smallint) FROM dbo.SplitParameters(@filtruCat,',') fart))) 
	) v
    WHERE 
    (@StocuriZero=1 OR Coalesce(Stoc,0)<>0) AND
    ((@Comparatie=0 AND Coalesce(Stoc,0) >@ValoareStoc) OR (@Comparatie=1 AND Coalesce(Stoc,0) >=@ValoareStoc) OR (@Comparatie=2 AND Coalesce(Stoc,0) =@ValoareStoc)
     OR (@Comparatie=3 AND Coalesce(Stoc,0) <=@ValoareStoc) OR (@Comparatie=4 AND Coalesce(Stoc,0) <@ValoareStoc) OR (@Comparatie=5 AND Coalesce(Stoc,0) <>@ValoareStoc))
     
END